package com.atung.tool;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.atung.model.AdmSchool;
import com.atung.model.BusProvinceCity;

import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;


@Service
public class Excel {
	private final static String EXCEL_2003L =".xls";    //2003- 版本的excel    
	private final static String EXCEL_2007U =".xlsx";   //2007+ 版本的excel  
	private static final String GD_GetAddressUrl="https://restapi.amap.com/v3/geocode/geo?address=%s"+"&key=de8da94f630309faa7e5b5e4694fea4e";

	 /**
     * 导入Excel
     */
    @Transactional(propagation = Propagation.REQUIRED)
    public List<AdmSchool> schoolExcel(MultipartFile file) throws Exception {  	
      //定义一个集合来存放数据
        List<AdmSchool> adminSchoolList=new ArrayList<>();
        //IO流读取文件
        InputStream in = file.getInputStream();
        String fileName=file.getOriginalFilename();
        //创建文档
        Workbook work=this.getWorkbook(in, fileName);
        in.close();
        if(null == work){    
            throw new Exception("导入的Excel工作薄为空！");    
        }  
        //读取文档，获取第一页，即sheet1
        Sheet sheet=work.getSheetAt(0);
        //读取row，从第二行开始，第一行为标题，不用读取
        int totalRows=sheet.getLastRowNum();//总行数
        //System.out.println(totalRows);
        for (int rownum = 1; rownum <totalRows+1; rownum++) {
            Row row=sheet.getRow(rownum);
            if (row!=null) {
                int totalCells=row.getLastCellNum();//每行的总列数
                AdmSchool admSchool=new AdmSchool();
                //读取列
                for (int c = 0; c < totalCells; c++) {
                    Cell cell=row.getCell(c);
                    if (c==0) {
                        if(this.getCellValue(cell).toString() !="")
                        	admSchool.setProvince(this.getCellValue(cell).toString());
                    }
                    if (c==1) {
                        if(this.getCellValue(cell).toString() !="")
                        	admSchool.setCity(this.getCellValue(cell).toString());          
                    }
                    if (c==2) {
                        if(this.getCellValue(cell).toString() !="")
                        	admSchool.setSchoolName(this.getCellValue(cell).toString());              
                    }
                    if (c==3) {
                        if(this.getCellValue(cell).toString() !="")
                        admSchool.setAddress(this.getCellValue(cell).toString());
                        String [] location=getLcatino(this.getCellValue(cell).toString());
                        if(!Objects.isNull(location)){
                        	admSchool.setLon(location[0]);
                        	admSchool.setLat(location[1]);
                        }
                    }
                 
                }
                adminSchoolList.add(admSchool);      
            }      
        }    
        return adminSchoolList;
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    public List<BusProvinceCity> cityExcel(MultipartFile file) throws Exception {  	

    	List<BusProvinceCity> busProvinceCityList=new ArrayList<>();
        //IO流读取文件
        InputStream in = file.getInputStream();
        String fileName=file.getOriginalFilename();
        //创建文档
        Workbook work=this.getWorkbook(in, fileName);
        in.close();
        if(null == work){    
            throw new Exception("导入的Excel工作薄为空！");    
        }  
        //读取文档，获取第一页，即sheet1
        Sheet sheet=work.getSheetAt(0);
        //读取row，从第二行开始，第一行为标题，不用读取
        int totalRows=sheet.getLastRowNum();//总行数
        for (int rownum = 1; rownum <totalRows+1; rownum++) {
            Row row=sheet.getRow(rownum);
            if (row!=null) {
                int totalCells=row.getLastCellNum();//每行的总列数
                BusProvinceCity busProvinceCity=new BusProvinceCity();	
                //读取列
                for (int c = 0; c < totalCells; c++) {
                    Cell cell=row.getCell(c);
                    if (c==0) {
                        if(this.getCellValue(cell).toString() !="")
                        	busProvinceCity.setProvince(this.getCellValue(cell).toString());
                    }
                    if (c==1) {
                        if(this.getCellValue(cell).toString() !="")
                        	busProvinceCity.setCity(this.getCellValue(cell).toString());          
                    }
     
                }
                busProvinceCityList.add(busProvinceCity);      
            }      
        }    
        return busProvinceCityList;
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    /**根据文件后缀选择版本
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public  Workbook getWorkbook(InputStream in,String fileName) throws Exception{    
        Workbook wb = null;    
        String fileType = fileName.substring(fileName.lastIndexOf("."));    
        if(EXCEL_2003L.equals(fileType)){    
            wb = new HSSFWorkbook(in);  //2003-    
        }else if(EXCEL_2007U.equals(fileType)){    
            wb = new XSSFWorkbook(in);  //2007+    
        }else{    
            throw new Exception("解析的文件格式有误！");    
        }
        return wb;    
    }
    /**格式化单元格数据
     * @param cell
     * @return
     */
    public  Object getCellValue(Cell cell){    
        Object value = null;    
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符    
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化    
        DecimalFormat df2 = new DecimalFormat("0.000000000");  //格式化数字    
             
        switch (cell.getCellType()) {    
        case Cell.CELL_TYPE_STRING:    
            value = cell.getRichStringCellValue().getString();    
            break;    
        case Cell.CELL_TYPE_NUMERIC:    
            if("General".equals(cell.getCellStyle().getDataFormatString())){    
                value = df.format(cell.getNumericCellValue());    
            }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){    
                value = sdf.format(cell.getDateCellValue());    
            }else{    
                value = df2.format(cell.getNumericCellValue());    
            }    
            break;    
        case Cell.CELL_TYPE_BOOLEAN:    
            value = cell.getBooleanCellValue();    
            break;    
        case Cell.CELL_TYPE_BLANK:    
            value = "";    
            break;    
        default:    
            break;    
        }    
        return value;    
     }
    
    public String[]  getLcatino(String address){
    	String url=String.format(GD_GetAddressUrl, address);
    	String response=HttpUtil.get(url);
    	JSONObject jsonBody =(JSONObject) JSONUtil.parse(response);
    	String count=jsonBody.get("count").toString();
    	if(!Objects.equals(count, "1")){
    		return null;
    	}
    	Object geocdes=(Object)jsonBody.get("geocodes");
    	String geocdesString=geocdes.toString();
    	JSONObject location =(JSONObject) JSONUtil.parse(geocdesString.substring(1, geocdesString.length()-1));
    	String AAA=(location.get("location").toString());
  	  	return AAA.split(",");
    }
}
